SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[spAgregarCampoTabla] 
@Tabla varchar(100),
@Campo varchar(100),
@long int,
@nulo varchar(1),
@Tipo varchar(40),
@tablaReferenciada varchar(60),
@tomaValores varchar(1)
AS
SET NOCOUNT ON
declare @sql varchar(2000)
declare @clavePrimaria varchar(50)
declare @sqlSelect varchar(70)

Select @clavePrimaria = name from syscolumns where id = object_id(@tablaReferenciada) and colorder = 1

select @sql = 'ALTER TABLE '+@Tabla+'  ADD '+ @Campo+' '+@Tipo

if upper(@Tipo) = 'VARCHAR'
	select @sql = @sql + '('+cast(@long as varchar(20))+')'

Select @sqlSelect = 'Select * from '+@Tabla
exec(@sqlSelect)

if upper(@nulo) = '0' and @@rowcount < 1
	select @sql = @sql + ' NOT NULL'
if @tomaValores = '1'
	select @sql = @sql + ' REFERENCES '+@tablaReferenciada+'('+@clavePrimaria+')'	

exec(@sql)
GO
